
import pymysql
import prettytable as pt

tb = pt.PrettyTable()

# 远程数据库管理账号，至少需要REPLICATION CLIENT,REPLICATION SLAVE权限进行binlog解析
KFZDBA = 'xxxxx'
KFZDBA_PASSWD = 'xxxxx'


# localdb 是执行备份存放的数据库
# 这里主要是把所有生成数据库实例信息写入数据库，检查和执行sql的时候，直接提供数据库实例名和数据库名称即可
# 会自动找到对应的地址端口然后拼装需要的 goinception SQL
def get_local_db():
    conn = pymysql.connect(host='127.0.0.1', user='root', password='Kfz@2099.com', port=3306, db='kfz_ops')
    return conn 

# goinception启动之后自带的数据库
def get_goinc_db():
    conn = pymysql.connect(host='127.0.0.1', user='', passwd='', db='', port=4000, charset="utf8mb4")
    return conn

# 数据库使用完毕，关闭连接
def close_db(conn, cursor):
    cursor.close()
    conn.close()

# 使用prettytable 进行结果输出
def print_table(cursor, result):
    tb = pt.PrettyTable()
    tb.field_names = [ i[0] for i in cursor.description ]
    for row in result:
        tb.add_row(row)
    return tb 

# 根据数据库实例名称查找对应的数据库地址，端口
def get_instance_address_port(instance_name):
    conn = get_local_db()
    cur = conn.cursor()
    instance_sql = "select domain, dbport from db_instance where instance_name = '{0}'".format(instance_name)
    instance_res = cur.execute(instance_sql)
    instance_result = cur.fetchone()
    close_db(conn, cur)
    return (instance_result[0], instance_result[1])

# SQL检测封装
def sql_check(instance, dbname, sql, pt=0):
    check_flag = False
    host, port = get_instance_address_port(instance)
    conn_inc = get_goinc_db() 
    cur_inc = conn_inc.cursor()
    # /**/ 中不能存在空格
    check_sql = """/*--host={0};--user={1};--password={2};--port={3};--check=1;*/inception_magic_start;use {4};{5};inception_magic_commit;""".format(host, KFZDBA, KFZDBA_PASSWD, port, dbname, sql)
    res = cur_inc.execute(check_sql)
    result = cur_inc.fetchall()
    error_list = [ item[2] for item in result ]
    
    # Key: 这里对结果进行判断是否都检测通过，如果都检测通过那么error_list都为0
    if len(set(error_list)) == 1 and set(error_list).pop() == 0:
        check_flag = True
    # 是否开启prettytable 打印
    if pt == 1:
        print(print_table(cur_inc, result))

    # close db 
    close_db(conn_inc, cur_inc)
    return check_flag

# SQL执行封装，默认不做备份，如果需要传入参数 backup=1
def sql_execute(instance, dbname, sql, backup=0, pt=0):
    execute_flag = False
    host, port = get_instance_address_port(instance)
    conn_inc = get_goinc_db()
    cur_inc = conn_inc.cursor()

    if backup:
        print("need to backup")
        check_sql = """/*--host={0};--user={1};--password={2};--port={3};--execute=1;--backup=1;*/inception_magic_start;use {4};{5};inception_magic_commit;""".format(host, KFZDBA, KFZDBA_PASSWD, port, dbname, sql)
    else: 
        check_sql = """/*--host={0};--user={1};--password={2};--port={3};--execute=1;--backup=0;*/inception_magic_start;use {4};{5};inception_magic_commit;""".format(host, KFZDBA, KFZDBA_PASSWD, port, dbname, sql)

    res = cur_inc.execute(check_sql)
    result = cur_inc.fetchall()
    error_list = [ item[2] for item in result ]

    if len(set(error_list)) == 1 and set(error_list).pop() == 0:
        execute_flag = True

    if pt == 1:
        print(print_table(cur_inc, result))

    return execute_flag 


if __name__ == '__main__':
    # 获取数据库实例和数据库名
    instance = "message"
    dbname = "message"

    # 测试SQL
    # sql = "alter table temp_kfz_lc101202 add column age2 int default 18 ;"
    # sql = '''insert into temp_kfz_lc101201(name)values('james-11'),('james-21'),('james-31');update temp_kfz_lc101201 set name = 'test-backup'  where id = 1 '''
    sql = '''drop table temp_kfz_lc101202 ;'''
    # 如果检测通过才能执行执行
    if sql_check(instance, dbname, sql.rstrip(';'), 1):
        print("checked and try to execute sql ...")
        # 判断是否执行成功
        if sql_execute(instance, dbname, sql.rstrip(';'), 1, 1):
            print("execute success!")
    else:
        print("sql checked failed !!! ")
